問題の説明
Excelで1つの列の「 ‑ 」で区切られたデータを複数に分割する (Divide data separated from ' ‑ ' in one column into more in excel)
Microsoft Excel を使用していますが、1 つの列に ‑ (スペース、ダッシュ、スペース) で区切られた 2 つの単語が含まれているファイルが与えられました。つまり、次のようなものがあります:
XXXXXX ‑ XXXXXXXXXXXX
XXX ‑ XXXXXXXX
XXXX ‑ XXX
これらの単語を 2 つの異なる列に分けたいと思います。どうやってやるの？よろしくお願いします。
リファレンスソリューション
方法 1:
You tagged the question with VBA but you've not posted any code.
A non VBA solution would be using TEXT TO COLUMNS. Just mark as delimiters the space and the other option (set it to ‑) and it will work properly:
When using TEXT TO COLUMNS, in step 1 choose Delimited. In step 2, do like this:
Third step, choose Finish
And you will get this:
方法 2:
You will need to loop the cells, using Split to separate the data using "‑" as the separator, and then write these to the appropriate cell.
Sub sSplitHyphen()
Dim lngRow As Long
Dim lngLast As Long
Dim aData() As String
lngLast = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLast
If InStr(ActiveSheet.Cells(lngRow, 1), "‑") > 0 Then
aData = Split(ActiveSheet.Cells(lngRow, 1), "‑")
ActiveSheet.Cells(lngRow, 2) = Trim(aData(0))
ActiveSheet.Cells(lngRow, 3) = Trim(aData(1))
End If
Next lngRow
End Sub
I've used Trim() to remove the leading/trailing spaces. If all of your data is separated by " ‑ " then you could use this to split the data.
Regards,
(by Maurizio Serra、Foxfire And Burns And Burns、Applecore)